using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;


namespace binary
{
    public class mySqlConnection
    {
        
        public SqlConnection Connection;

        public mySqlConnection(ConfigSingle config)
        {
            string _connectionString = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", config.host, config.dbName, config.user, config.password);
            Connection = new SqlConnection(_connectionString);
            try
            {
                Connection.Open();
            }
            catch (Exception ex)
            {
                 MessageBox.Show(ex.Message, "SQL - Konstuktor");
            }
        }


        public DataTable Query(string query)
        {
            SqlDataAdapter da = new SqlDataAdapter(query, Connection);
            DataSet ds = new DataSet();
            da.Fill(ds, "wynik");
            DataTable dt = ds.Tables["wynik"];
            ds.Dispose();
            da.Dispose();
            return dt;
        }

        public void QueryNoResult(string query)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(query, this.Connection);
                cmd.CommandTimeout = 999999;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("{0}:{1}",ex.Message,query),"Zapytanie SQL");
            }
        }
        
        public void Disconnect()
        {
            Connection.Close();
        }


        public myPoleSQL pobierzPoleSQL(string baza, string tabela, string nazwa)
        {
            myPoleSQL pole = new myPoleSQL();
            string query = @"

            SELECT  
	            table_catalog
	            ,table_schema
	            ,table_name
	            ,column_name
	            ,ordinal_position
	            ,data_type
	            ,character_maximum_length
	            ,numeric_precision
	            ,numeric_scale
            FROM   
              INFORMATION_SCHEMA.COLUMNS 
            WHERE   
              table_catalog = '{3}'
              and TABLE_NAME = '{2}' 
              and table_schema = 'dbo'
              and (column_name = '{0}' or column_name = '_{1}' )
            ";

            query = query.Replace("{0}", nazwa);
            query = query.Replace("{1}", nazwa);
            query = query.Replace("{2}", tabela);
            query = query.Replace("{3}", baza);

            DataTable dt = Query(query);
            if (dt.Rows.Count > 0)
            {
                pole.table_schema = dt.Rows[0]["table_schema"].ToString();
                pole.table_name = dt.Rows[0]["table_name"].ToString();
                pole.table_catalog = dt.Rows[0]["table_catalog"].ToString();

                if (dt.Rows[0]["ordinal_position"].ToString().Length > 0)
                {
                    pole.ordinal_position = int.Parse(dt.Rows[0]["ordinal_position"].ToString());
                }

                if (dt.Rows[0]["numeric_scale"].ToString().Length > 0)
                {
                    pole.numeric_scale = int.Parse(dt.Rows[0]["numeric_scale"].ToString());
                }

                if (dt.Rows[0]["numeric_precision"].ToString().Length > 0)
                {
                    pole.numeric_precision = int.Parse(dt.Rows[0]["numeric_precision"].ToString());
                }

                pole.data_type = dt.Rows[0]["data_type"].ToString();
                pole.column_name = dt.Rows[0]["column_name"].ToString();

                if (dt.Rows[0]["character_maximum_length"].ToString().Length > 0)
                {
                    pole.character_maximum_length = int.Parse(dt.Rows[0]["character_maximum_length"].ToString());
                }
            }
            else
            {
                return null;
            }
            return pole;
        }

        public List<myPoleSQL> pobierzPolaSQLWszystkie(string tabela)
        {
            List<myPoleSQL> lista = new List<myPoleSQL>();
            
            string query = @"

            SELECT  
	            table_catalog
	            ,table_schema
	            ,table_name
	            ,column_name
	            ,ordinal_position
	            ,data_type
	            ,character_maximum_length
	            ,numeric_precision
	            ,numeric_scale
            FROM   
              INFORMATION_SCHEMA.COLUMNS 
            WHERE   
              TABLE_NAME = '{0}' 
              and table_schema = 'dbo'
            ";

            query = query.Replace("{0}", tabela);

            DataTable dt = Query(query);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    myPoleSQL pole = new myPoleSQL();
                    pole.table_schema = row["table_schema"].ToString();
                    pole.table_name = row["table_name"].ToString();
                    pole.table_catalog = row["table_catalog"].ToString();

                    if (row["ordinal_position"].ToString().Length > 0)
                    {
                        pole.ordinal_position = int.Parse(row["ordinal_position"].ToString());
                    }

                    if (row["numeric_scale"].ToString().Length > 0)
                    {
                        pole.numeric_scale = int.Parse(row["numeric_scale"].ToString());
                    }

                    if (row["numeric_precision"].ToString().Length > 0)
                    {
                        pole.numeric_precision = int.Parse(row["numeric_precision"].ToString());
                    }

                    pole.data_type = row["data_type"].ToString();
                    pole.column_name = row["column_name"].ToString();

                    if (row["character_maximum_length"].ToString().Length > 0)
                    {
                        pole.character_maximum_length = int.Parse(row["character_maximum_length"].ToString());
                    }
                    lista.Add(pole);
                    pole.Dispose();
                }
            }
            else
            {
                return null;
            }
            return lista;
        }

        public void insertImportLog(string table, ImportLog log)
        {
            string query = String.Format("insert {0} select ", table);
            query += String.Format("'{0}'", log.Table);
            query += String.Format(",{0}", log.LoadingTime.ToString().Replace(",","."));
            query += String.Format(",'{0}'", log.LoadingStart);
            query += String.Format(",'{0}'", log.LoadingFinish);
            query += String.Format(",{0}", log.DbfRecords);
            query += String.Format(",{0}", log.SqlRecords);
            query += String.Format(",'{0}'", DateTime.Now);
            this.QueryNoResult(query);
            
        }

        public int getRowsNumber(string table)
        {
            string query = String.Format("select count(*) from {0}", table);
            DataTable result = this.Query(query);
            if (result.Rows.Count == 1)
            {
                return int.Parse(result.Rows[0][0].ToString());
            }
            return -1;
        }

        public void insertErrors(string table, List<ImportError> list)
        {
            string query = "";
            foreach (ImportError err in list)
            {
                query = String.Format("insert {0} select ", (String.IsNullOrEmpty(table)) ? "brak" : table);
                query += String.Format("'{0}'", DateTime.Now);
                query += String.Format(",'{0}'", err.TableName);
                query += String.Format(",'{0}'", err.ColumnName);
                query += String.Format(",{0}", err.Record);
                query += String.Format(",'{0}'", err.Description.Replace("'","''"));
                this.QueryNoResult(query);
            }

        }





    }
}
